﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticleTag : ICmsArticleTag
    {

        private const string ADD_SQL = "insert into CMS_ARTICLE_TAG(TAG_NAME,STATE,ADD_TIME,USER_ID,CLICK_NUM,IS_HOTS,HOTS_TYPE,CLASSIDS,TAG_COLOR) values(@TAG_NAME,@STATE,getDate(),@USER_ID,0,@IS_HOTS,@HOTS_TYPE,@CLASSIDS,@TAG_COLOR);select @@IDENTITY";
        //添加
        public int Add(CmsArticleTagModel model)
        {

            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@TAG_NAME", SqlDbType.VarChar,50),
                new SqlParameter("@STATE",SqlDbType.Int,4),
                new SqlParameter("@USER_ID",SqlDbType.Int,4),
                new SqlParameter("@IS_HOTS",SqlDbType.Int,4),
                new SqlParameter("@HOTS_TYPE",SqlDbType.VarChar,50),
                new SqlParameter("@CLASSIDS",SqlDbType.VarChar,100),
                new SqlParameter("@TAG_COLOR",SqlDbType.VarChar,50)
            };
            parms[0].Value = model.TagName;
            parms[1].Value = model.State;
            parms[2].Value = model.UserId;
            parms[3].Value = model.IsHots;
            parms[4].Value = model.HotsType;
            parms[5].Value = model.ClassIds;
            parms[6].Value = model.TagColor;
            

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();

                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }

        private const string UPDATE_SQL = "update CMS_ARTICLE_TAG set TAG_NAME=@TAG_NAME,STATE=@STATE,IS_HOTS=@IS_HOTS,HOTS_TYPE=@HOTS_TYPE,CLASSIDS=@CLASSIDS,TAG_COLOR=@TAG_COLOR where TAG_ID=@TAG_ID";
        //修改基本资料
        public bool Update(CmsArticleTagModel model)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                
                new SqlParameter("@TAG_NAME",SqlDbType.VarChar,50),
                
                new SqlParameter("@STATE",SqlDbType.Int,4),
                
                //new SqlParameter("@CLICK_NUM", SqlDbType.Int, 4),
                new SqlParameter("@TAG_ID", SqlDbType.Int, 4),
                new SqlParameter("@IS_HOTS",SqlDbType.Int,4),
                new SqlParameter("@HOTS_TYPE",SqlDbType.VarChar,50),
                new SqlParameter("@CLASSIDS",SqlDbType.VarChar,100),
                new SqlParameter("@TAG_COLOR",SqlDbType.VarChar,50)
            };

            parms[0].Value = model.TagName;

            parms[1].Value = model.State;

            //parms[2].Value = model.ClickNum;
            parms[2].Value = model.TagId;
            parms[3].Value = model.IsHots;
            parms[4].Value = model.HotsType;
            parms[5].Value = model.ClassIds;
            parms[6].Value = model.TagColor;
            
            int i = SQLHelper.ExecuteSql(UPDATE_SQL, parms);
            if (i > 0) return true;
            return false;
        }
        //查询单个，返回model
        private const string GET_TAG_SQL = "select TAG_NAME,STATE,ADD_TIME,USER_ID,CLICK_NUM,t.IS_HOTS,t.HOTS_TYPE,t.CLASSIDS,t.TAG_COLOR from CMS_ARTICLE_TAG t where TAG_ID = @TAG_ID";
        public CmsArticleTagModel GetTagModel(int tid)
        {
            CmsArticleTagModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@TAG_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = tid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_TAG_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new CmsArticleTagModel();
                    mim.TagId = tid;
                    if (!rdr.IsDBNull(0)) mim.TagName = rdr.GetString(0);
                    if (!rdr.IsDBNull(1)) mim.State = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) mim.AddTime = rdr.GetDateTime(2);
                    if (!rdr.IsDBNull(3)) mim.UserId = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) mim.ClickNum = rdr.GetInt32(4);
                    if (!rdr.IsDBNull(5)) mim.IsHots = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) mim.HotsType = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.ClassIds = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.TagColor = rdr.GetString(8);
                   
                }
            }
            return mim;
        }
        //查询，返回list条件model
        public IList GetTagList(CmsArticleTagModel q)
        {
            string qsql = "select TAG_NAME,t.STATE,ADD_TIME,USER_ID,CLICK_NUM,TAG_ID,u.USERNAME,t.IS_HOTS,t.HOTS_TYPE,t.CLASSIDS from CMS_ARTICLE_TAG t left join cms_users u on t.USER_ID=u.aid where 1=1 ";

            if (q != null)
            {
                if (q.TagName != null && q.TagName != "")
                {
                    qsql += " and TAG_NAME like '%" + StringUtil.ReplaceSecStr(q.TagName) + "%'";
                }
                if (q.State > 0)
                {
                    qsql += " and state=" + q.State;
                }
                if (q.ClassIds != null && q.ClassIds != "")
                {
                    qsql += " and t.CLASSIDS like '%" + q.ClassIds + "%'";
                }
                if (q.IsHots > 0)
                {
                    qsql += " and t.IS_HOTS=1";
                }
            }
            qsql += " order by t.ADD_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while(rdr.Read())
                {
                    CmsArticleTagModel mim = new CmsArticleTagModel();
                   
                    mim.TagName = rdr.GetString(0);
                    mim.State = rdr.GetInt32(1);
                    mim.AddTime = rdr.GetDateTime(2);
                    mim.UserId = rdr.GetInt32(3);
                    mim.ClickNum = rdr.GetInt32(4);
                    mim.TagId = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) mim.UserName = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.IsHots = rdr.GetInt32(7);
                    if (!rdr.IsDBNull(8)) mim.HotsType = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.ClassIds = rdr.GetString(9);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }
        //查询，返回list条件model(判断重名使用)
        public IList GetTag(CmsArticleTagModel q)
        {
            string qsql = "select TAG_NAME,t.STATE,ADD_TIME,USER_ID,CLICK_NUM,TAG_ID,u.USERNAME,t.IS_HOTS,t.HOTS_TYPE,t.CLASSIDS from CMS_ARTICLE_TAG t left join cms_users u on t.USER_ID=u.aid where 1=1 ";

            if (q != null)
            {
                if (q.TagName != null && q.TagName != "")
                {
                    qsql += " and TAG_NAME = '" + StringUtil.ReplaceSecStr(q.TagName) + "'";
                }
                else
                { 
                     IList il = new ArrayList();
                     return il;
                }
            }
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleTagModel mim = new CmsArticleTagModel();

                    mim.TagName = rdr.GetString(0);
                    mim.State = rdr.GetInt32(1);
                    mim.AddTime = rdr.GetDateTime(2);
                    mim.UserId = rdr.GetInt32(3);
                    mim.ClickNum = rdr.GetInt32(4);
                    mim.TagId = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) mim.UserName = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.IsHots = rdr.GetInt32(7);
                    if (!rdr.IsDBNull(8)) mim.HotsType = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.ClassIds = rdr.GetString(9);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }
        //查询，返回list 条件字符串
        public IList GetTagList(string tids)
        {
            string qsql = "select TAG_NAME,t.STATE,ADD_TIME,USER_ID,CLICK_NUM,TAG_ID,USERNAME,t.IS_HOTS,t.HOTS_TYPE,t.CLASSIDS from CMS_ARTICLE_TAG t left join cms_users u on t.USER_ID=u.aid where TAG_ID in(" + tids + ")";

            qsql += " order by t.ADD_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleTagModel mim = new CmsArticleTagModel();
                    
                    mim.TagName = rdr.GetString(0);
                    mim.State = rdr.GetInt32(1);
                    mim.AddTime = rdr.GetDateTime(2);
                    mim.UserId = rdr.GetInt32(3);
                    mim.ClickNum = rdr.GetInt32(4);
                    mim.TagId = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) mim.UserName = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.IsHots = rdr.GetInt32(7);
                    if (!rdr.IsDBNull(8)) mim.HotsType = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.ClassIds = rdr.GetString(9);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }
        //删除
        public bool Delete(int tid)
        {
            int i = SQLHelper.ExecuteSql("delete from CMS_ARTICLE_TAG where TAG_ID=" + tid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }

        public bool AddNum(int tid)
        {
            int i = SQLHelper.ExecuteSql("update CMS_ARTICLE_TAG set CLICK_NUM=CLICK_NUM+1 where TAG_ID=" + tid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }

    }
}
